/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Modelo.DAL;

import Modelo.Grupo;
import org.json.JSONObject;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import Modelo. Bono;
import Modelo.Asistencia;
/**
 *
 * @author yngrdyn
 */
public class Bono_DAO {

    public Bono_DAO() {
    }

    public boolean insertarBono(Bono b) {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;

        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error Insertando Bono!");
            return false;
        }
        String query = "INSERT INTO bono(id_bono,tipo,sede,se_asignapor)"
                + "VALUES ('" + b.getBono() + "','" + b.getTipo() + "','" + b.getSede() + "','" + b.getAsigna() + "')";
        System.out.println(query);
        System.out.println("\n\tQuery: " + query + "\n");

        try {
            s.executeUpdate(query);
            return true;
        } catch (SQLException se) {
            System.out.println("Error Insertando Bono!" + se.getMessage());
            return false;
        }
    }

    public boolean generarBonoTransporte() {
        ConexionBD conexion = new ConexionBD();
        Statement s = null;

        try {
            s = conexion.session.createStatement();
        } catch (SQLException se) {
            System.out.println("Error Insertando Bono!");
            return false;
        }
        String query = "delete from se_asigna_bono";
        System.out.println("\n\tQuery: " + query + "\n");
        try {
            s.execute(query);
        } catch (SQLException se) {
            System.out.println("Error generando Bono!" + se.getMessage());
            return false;
        }

        query = "insert into se_asigna_bono(ci,total,id_bono,se_asignapor,sede)"
                + "(select ci,count(*),'Transporte','dias',sede from asistencia a,guardia g where g.id_guardia = a.id_guardia and presente=TRUE group by ci,sede)";

        System.out.println("\n\tQuery: " + query + "\n");

        try {
            s.executeUpdate(query);
            return true;
        } catch (SQLException se) {
            System.out.println("Error generando Bono!" + se.getMessage());
            return false;
        }
    }

    public JSONObject consultarTipoBono(String id_bono, String sede) {
        ConexionBD conexion = new ConexionBD();
        JSONObject tiposObjeto = new JSONObject();
        Statement s = null;

        try {
            s = conexion.session.createStatement();
        String query = "select a.ci,nombre_1,apellido_1,total,se_asignapor from se_asigna_bono a,comunidad_universitaria cu where a.ci = cu.ci and id_bono='"+id_bono+"' and sede = '"+sede+"'";

        System.out.println("\n\tQuery: " + query + "\n");

        ResultSet rs = s.executeQuery(query);
            int i = 0;
            while (rs.next()) {
                JSONObject tipoObjeto = new JSONObject();
                tipoObjeto.put("ci", rs.getString(1));
                tipoObjeto.put("nombre_1", rs.getString(2));
                tipoObjeto.put("apellido_1", rs.getString(3));
                tipoObjeto.put("total", rs.getString(4));
                tipoObjeto.put("se_asignapor", rs.getString(5));
                tiposObjeto.put("" + i++, tipoObjeto);
            }
            System.out.println(tiposObjeto.length());
        } catch (Exception e) {
            System.out.print("me potee "+ e.getMessage());
        }
        return tiposObjeto;
    }

}